﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace produccion.clases
{
    public class CLSMAEREF:ObservableObject
    {
        MDOgeneral mod = new MDOgeneral();
        CLSMAECOL MaeCol = new CLSMAECOL();

        #region propiedades
        string codref_ref, codcol_ref, nomref_ref;
        public string CODREF_REF
        {
            get { return codref_ref; }
            set {
            codref_ref = value;
            RaisePropertyChangedEvent("CODREF_REF");
            }
        }
        public string CODCOL_REF
        {
            get { return codcol_ref; }
            set {
            if (codcol_ref != value) { CAMBIO = true; } 
            codcol_ref = value;
            RaisePropertyChangedEvent("CODCOL_REF");
            }
        }
        public string NOMREF_REF
        {
            get { return nomref_ref; }
            set {
            if (nomref_ref != value) { CAMBIO = true; } 
            nomref_ref = value;
            RaisePropertyChangedEvent("NOMREF_REF");
            }
        }
        private Boolean _CAMBIO = false;
        public Boolean CAMBIO
        {
            get { return _CAMBIO; }
            set
            {
                _CAMBIO = value;
                RaisePropertyChangedEvent("CAMBIO");
            }
        }
        private Boolean _EXISTE = false;
        public Boolean EXISTE
        {
            get { return _EXISTE; }
            set
            {
                _EXISTE = value;
                RaisePropertyChangedEvent("EXISTE");
            }
        }

        public CLSMAECOL infocol = new CLSMAECOL();
        public CLSMAEPRVP infproveedor = new CLSMAEPRVP();
        #endregion

        #region metodos

        public List<CLSMAEREF> listaReferencias()
        {
            try
            {
                string query = "select codref_ref, nomref_ref from USU_DBA.maeref";
                DataTable val = mod.consulta(query);
                List<CLSMAEREF> lisRefe = new List<CLSMAEREF>();
                foreach (DataRow r in val.Rows)
                {
                    lisRefe.Add(new CLSMAEREF
                    {
                        CODREF_REF = r["CODREF_REF"].ToString().Trim(),
                        NOMREF_REF = r["NOMREF_REF"].ToString(),
                        CODCOL_REF = "",
                        CAMBIO = false,
                        EXISTE = true
                    });
                }
                return lisRefe;
            }
            catch (Exception e) { throw e; }
        }

        public void LimpiarValores()
        {
            CODREF_REF = "";
            CODCOL_REF = "";
        }
        public Boolean ExisteReferencia()
        {
            try
            {
                string query = "select codref_ref from maeref where codref_ref='" + CODREF_REF + "'";
                if (mod.consulta(query).Rows.Count > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public Boolean ExisteReferenciaPo1()
        {
            try
            {
                string query = "select codref_ref from maeref where codprv_ref='01' and codref_ref='" + CODREF_REF + "'";
                if (mod.consulta(query).Rows.Count > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable ConsultarRefeoConc()
        {
            try
            {
                string query = "";
                if (CODREF_REF.Trim().Length < 5)
                    query = "select codref_ref as Codigo, nomref_ref as Nombre from maeref where codref_ref='" + CODREF_REF + "'";
                else
                {
                    query = "select b.codref_ref as Codigo, b.nomref_ref as Nombre from colref a inner join maeref b on a.codref_cxr=b.codref_ref where b.nomref_ref='" + CODREF_REF + "'";
                }
                DataTable dt = mod.consulta(query);
                if (dt.Rows.Count > 0)
                {
                    NOMREF_REF = dt.Rows[0][1].ToString();
                    CODREF_REF = dt.Rows[0][0].ToString();
                    EXISTE = true;
                    CAMBIO = false;
                }
                else
                {
                    //Si no existe como referencia se busca el concepto
                    string query1 = "";
                    if (CODREF_REF.Trim().Length >= 3)
                    {
                        query1 = "select codcpt_cpt as Codigo, nomcpt_cpt as Nombre from concep where trim(codcpt_cpt)=trim('" + CODREF_REF + "')";

                        DataTable dt1 = mod.consulta(query1);
                        if (dt1.Rows.Count > 0)
                        {
                            NOMREF_REF = dt1.Rows[0][1].ToString();
                            CODREF_REF = dt1.Rows[0][0].ToString();
                            EXISTE = true;
                            CAMBIO = false;
                            return dt1;
                        }
                        else
                        {
                            query1 = "select codprc_pes as Codigo, nomprc_pes as Nombre from prcesp where trim(codprc_pes)=trim('" + CODREF_REF + "')";

                            DataTable dt2 = mod.consulta(query1);
                            if (dt1.Rows.Count > 0)
                            {
                                NOMREF_REF = dt2.Rows[0][1].ToString();
                                CODREF_REF = dt2.Rows[0][0].ToString();
                                EXISTE = true;
                                CAMBIO = false;
                                return dt2;
                            }
                            else
                            {
                                NOMREF_REF = "";
                                EXISTE = false;
                            }
                        }
                    }
                    else
                    {
                        NOMREF_REF = "";
                        EXISTE = false;
                    }
                }
                
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable Consultar()
        {
            try
            {
                string query = "";
                if (String.IsNullOrEmpty(CODREF_REF) == false)
                {
                    if (CODREF_REF.Trim().Length < 5)
                        query = "select codref_ref as Codigo, nomref_ref as Nombre from maeref where codref_ref='" + CODREF_REF + "'";
                    else
                    {
                        query = "select b.codref_ref as Codigo, b.nomref_ref as Nombre from colref a inner join maeref b on a.codref_cxr=b.codref_ref where b.nomref_ref='" + CODREF_REF + "'";
                    }
                    DataTable dt = mod.consulta(query);
                    if (dt.Rows.Count > 0)
                    {
                        NOMREF_REF = dt.Rows[0][1].ToString();
                        CODREF_REF = dt.Rows[0][0].ToString();
                        EXISTE = true;
                        CAMBIO = false;
                    }
                    else
                    {
                        NOMREF_REF = "";
                        EXISTE = false;
                    }
                    return dt;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable ConsultarP01()
        {
            try
            {
                string query = "";
                if (CODREF_REF.Trim().Length < 5)
                    query = "select codref_ref as Codigo, nomref_ref as Nombre from maeref where codprv_ref='01' and codref_ref='" + CODREF_REF + "'";
                else
                {
                    query = "select b.codref_ref as Codigo, b.nomref_ref as Nombre from colref a inner join maeref b on a.codref_cxr=b.codref_ref where b.codprv_ref='01' and b.nomref_ref='" + CODREF_REF + "'";
                }
                DataTable dt = mod.consulta(query);
                if (dt.Rows.Count > 0)
                {
                    NOMREF_REF = dt.Rows[0][1].ToString();
                    CODREF_REF = dt.Rows[0][0].ToString();
                    EXISTE = true;
                    CAMBIO = false;
                }
                else
                {
                    NOMREF_REF = "";
                    EXISTE = false;
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable Buscar()
        {
            try
            {
                string query = "select trim(a.CODREF_REF) as Codigo, trim(a.NOMREF_REF) as Nombre, c.nomprv_prv as Proveedor from maeref a inner join maerefp b on a.CODREF_REF=b.CODREF_REF inner join USU_DBA.maeprv c on a.codprv_ref=c.codprv_prv where  upper(a.NOMREF_REF) like '%" + NOMREF_REF.ToUpper() + "%' order by nomref_ref asc";
                return mod.consulta(query);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable BuscarP01()
        {
            try
            {
                string query = "select codref_ref as Codigo, nomref_ref as Nombre from maeref where codprv_ref='01' and UPPER(nomref_ref) like '%" + NOMREF_REF.ToUpper() + "%' order by nomref_ref asc";
                return mod.consulta(query);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable buscarTallas()
        {
            try
            {
                string query = "select b.codtal_tal from maeref a inner join taltal b on a.talref_ref = b.codtje_tal where codref_ref ='" + CODREF_REF.ToUpper() + "' order by b.postal_tal asc";
                return mod.consulta(query);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable buscarTallasColor(string strColor)
        {
            try
            {
                string query = "select codtal_pro from maepro where codref_pro = '" + CODREF_REF + "' and codcol_pro = '" + strColor + "' order by codtal_pro asc";
                return mod.consulta(query);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int TallajeRef(string Ref)
        {
            try
            {
                DataTable dt = new DataTable();
                string query = "select talref_ref from maeref where codref_ref='" + Ref + "'";
                dt = mod.consulta(query);
                if (dt.Rows.Count > 0)
                {
                    return System.Convert.ToInt16(dt.Rows[0][0].ToString());
                }
                return 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        public DataTable repProPro = new DataTable();
        public DataTable repProProExcel = new DataTable();

        public void RepProPro(string codsec, string codcli, string pva_sepped, string pva_sepord, string pva_linmon, string pva_incgar, string pva_incprd, string codcol, string strRefe, string pva_incdev, string pva_increp, string pva_increc, string pva_incser)
        {
            try
            {
                MDOgeneral.spComando.CommandText = "SP_INFPROPRC";
                MDOgeneral.spComando.Connection = MDOgeneral.conexion;
                MDOgeneral.spComando.CommandType = CommandType.StoredProcedure;
                MDOgeneral.spComando.Parameters.Clear();
                //Input
                MDOgeneral.spComando.Parameters.Add("pva_codsec", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = codsec;
                MDOgeneral.spComando.Parameters["pva_codsec"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pva_cliini", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = String.IsNullOrEmpty(codcli) ? "   " : codcli;
                MDOgeneral.spComando.Parameters["pva_cliini"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pva_clifin", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = String.IsNullOrEmpty(codcli) ? "ZZZ" : codcli;
                MDOgeneral.spComando.Parameters["pva_clifin"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pva_sepped", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value =pva_sepped;
                MDOgeneral.spComando.Parameters["pva_sepped"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pva_sepord", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value =pva_sepord;
                MDOgeneral.spComando.Parameters["pva_sepord"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pva_linmon", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value =pva_linmon;
                MDOgeneral.spComando.Parameters["pva_linmon"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pva_incgar", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value =pva_incgar;
                MDOgeneral.spComando.Parameters["pva_incgar"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pva_incprd", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value =pva_incprd;
                MDOgeneral.spComando.Parameters["pva_incprd"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pva_clcini", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = String.IsNullOrEmpty(codcol) ? "  " : codcol;
                MDOgeneral.spComando.Parameters["pva_clcini"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pva_clcfin", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = String.IsNullOrEmpty(codcol) ? "ZZ" : codcol;
                MDOgeneral.spComando.Parameters["pva_clcfin"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pva_refini", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = String.IsNullOrEmpty(strRefe) ? "    " : strRefe;
                MDOgeneral.spComando.Parameters["pva_refini"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pva_reffin", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = String.IsNullOrEmpty(strRefe) ? "ZZZZ" : strRefe;
                MDOgeneral.spComando.Parameters["pva_reffin"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pva_incdev", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = pva_incdev;
                MDOgeneral.spComando.Parameters["pva_incdev"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pva_increp", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = pva_increp;
                MDOgeneral.spComando.Parameters["pva_increp"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pva_increc", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = pva_increc;
                MDOgeneral.spComando.Parameters["pva_increc"].Direction = ParameterDirection.Input;

                MDOgeneral.spComando.Parameters.Add("pva_incser", Oracle.DataAccess.Client.OracleDbType.Varchar2, 40).Value = pva_incser;
                MDOgeneral.spComando.Parameters["pva_incser"].Direction = ParameterDirection.Input;
                //Output
                //MDOgeneral.spComando.Parameters.Add("pva_result", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Direction = ParameterDirection.Output;
                //MDOgeneral.spComando.Parameters.Add("pva_errusu", Oracle.DataAccess.Client.OracleDbType.Varchar2, 2000).Direction = ParameterDirection.Output;
                
                MDOgeneral.spComando.ExecuteNonQuery();
                //string c = MDOgeneral.spComando.Parameters["pva_result"].Value.ToString();
                //string query = "select CODCLI_TMP,NOMCLI_TMP,NROORD_TMP,CODREF_TMP,NOMREF_TMP,CODCOL_TMP,NOMCOL_TMP,T1_TMP,T2_TMP,T3_TMP,T4_TMP,T5_TMP,T6_TMP,T7_TMP,T8_TMP,T9_TMP,T10_TMP,T11_TMP,T12_TMP,T13_TMP,T14_TMP,T15_TMP,T16_TMP,T17_TMP,T18_TMP,T19_TMP,T20_TMP,TOTAL_TMP,CODLINMON_TMP,NOMLINMON_TMP,NROPED_TMP,TALREF_REF FROM tmpinfproprc,maeref WHERE CODREF_TMP=codref_ref order by NROORD_TMP";
                string query = "SELECT CODCLI_TMP,NOMCLI_TMP,NROORD_TMP,CODREF_TMP,NOMREF_TMP,CODCOL_TMP,NOMCOL_TMP,T1_TMP,T2_TMP,T3_TMP,T4_TMP,T5_TMP,T6_TMP,T7_TMP,T8_TMP,T9_TMP,T10_TMP,T11_TMP,T12_TMP,T13_TMP,T14_TMP,T15_TMP,T16_TMP,T17_TMP,T18_TMP,T19_TMP,T20_TMP,TOTAL_TMP,CODLINMON_TMP,NOMLINMON_TMP,NROPED_TMP,TALREF_REF FROM tmpinfproprc,maeref WHERE CODREF_TMP=codref_ref order by CODLINMON_TMP, CODCLI_TMP, NROORD_TMP";
                repProPro = mod.consulta(query);

                query = "SELECT CODCLI_TMP AS CODIGOCLIENTE,NOMCLI_TMP AS NOMBRECLIENTE,NROORD_TMP AS NUMEROORDEN,CODREF_TMP AS CODIGOREFERENCIA,NOMREF_TMP AS NOMBREREFERENCIA,CODCOL_TMP AS CODIGOCOLOR,NOMCOL_TMP AS NOMBRECOLOR,T1_TMP AS T1,T2_TMP AS T2,T3_TMP AS T3,T4_TMP AS T4,T5_TMP AS T5,T6_TMP AS T6,T7_TMP AS T7,T8_TMP AS T8,T9_TMP AS T9,T10_TMP AS T10,T11_TMP AS T11,T12_TMP AS T12,T13_TMP AS T13,T14_TMP AS T14,T15_TMP AS T15,T16_TMP AS T16,T17_TMP AS T17,T18_TMP AS T18,T19_TMP AS T19,T20_TMP AS T20,TOTAL_TMP AS TOTAL,CODLINMON_TMP AS CODIGOLINEAMONTAJE,NOMLINMON_TMP AS NOMBRELINEAMONTAJE,NROPED_TMP AS NROPEDIDO,TALREF_REF AS TALLAREFERENCIA FROM tmpinfproprc,maeref WHERE CODREF_TMP=codref_ref order by CODLINMON_TMP, CODCLI_TMP, NROORD_TMP";
                repProProExcel = mod.consulta(query);
            }
            catch (Exception ex) { throw ex; }
        }



    }
}
